﻿<TranslationFunctionSpec
	xmlns="bb_appfx_translationfunction"
	xmlns:c="bb_appfx_commontypes"
	ID="a317d7b1-8ea3-46ea-b04d-1f713b7efb2c"
	Name="TDSM Organization Hierarchy Translation Function"
	Description="Translates an Organization Hierarchy ID in to a name"
	Author="Blackbaud Professional Services"
	RecordType="Organization"
	>

	<SQLTranslationFunction FNName="USR_UFN_TRANSLATIONFUNCTION_TDSM_ORGHIERARCHY">
		<c:CreateFunctionSQL>
			<![CDATA[
create function dbo.USR_UFN_TRANSLATIONFUNCTION_TDSM_ORGHIERARCHY(@ID uniqueidentifier)
returns nvarchar(max) 
as
begin
  --based on USP_DATALIST_ORGANIZATIONHIERARCHY
  declare @ORGANIZATIONID uniqueidentifier = 'C090DCA2-F496-4C0D-8775-BD01B92C7948';
  declare @result nvarchar(max) = null;
  
  if @organizationid = @id begin
    select @result = dbo.UFN_INSTALLATIONINFO_GETINSTALLATIONNAME();
  end
  else begin
    select @result = ORGANIZATIONPOSITION.NAME
	  from
      dbo.ORGANIZATIONHIERARCHY
	    inner join
	    dbo.ORGANIZATIONPOSITION
      on ORGANIZATIONPOSITION.ID = ORGANIZATIONHIERARCHY.ID
	  where ORGANIZATIONHIERARCHY.ID = @ID;
  end;
  
  return @result;
end;
]]>
		</c:CreateFunctionSQL>
	</SQLTranslationFunction>
</TranslationFunctionSpec>